set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions=200;
set hive.exec.max.dynamic.partitions.pernode=200;

insert overwrite table jms_dm.dm_waybill_false_taking_summary_dt partition (dt)
select
    intime.taking_date
    , intime.receive_network_code
    , intime.receive_network_name
    , intime.receive_franchisee_code        as receive_fran_code
    , intime.receive_franchisee             as receive_fran_name
    , intime.receive_proxy_code             as receive_agent_code
    , intime.receive_proxy                  as receive_agent_name
    , dim_network.virt_code                 as receive_virt_agent_code
    , dim_network.virt_name                 as receive_virt_agent_name
    , intime.order_source                   as order_source_code
    , intime.taking_user_code
    , intime.taking_user_name
    , intime.customer_code
    , intime.customer_name
    , count(case when intime.is_over is not null and intime.type = 0 then intime.waybill end)  as receive_network_taking_cnt  -- 网点揽收数量
    , count(case when intime.type = 0 then intime.actual_send_time end)                        as receive_network_send_cnt    -- 网点发件数量
    , count(case when intime.type = 0 then intime.actual_collect_arr_time end)                 as collect_arr_cnt             -- 集散到件数量
    , count(case when intime.type = 0 then intime.actual_center_arr_time end)                  as center_arr_cnt              -- 中心到件数量
    , count(false_taking.waybill_no)                                                           as receive_network_false_taking_cnt   -- 网点全部虚假揽收票数
    , count(case when intime.is_over is not null and intime.type = 0 then intime.waybill end)  as receive_network_should_hand_in_cnt -- 网点全部应交件票数
    , count(case when false_taking.is_break = 1 then 1 end)                                    as interrupted_taking_cnt      -- 截单后揽收数
    , count(case when false_taking.false_taking_type = 0 then 1 end)                           as false_taking_no_send_cnt    -- 虚假揽收未发件数
    , count(case when false_taking.false_taking_type = 1 then 1 end)                           as false_taking_no_hand_in_cnt -- 虚假揽收未到件数
    , sum(
        unix_timestamp(false_taking.first_network_send_time) - unix_timestamp(false_taking.first_taking_scan_time)
      )      as taking_duration_hour  -- 揽收时长(只看虚假揽收,单位现在改为秒)
    , count(case when intime.type = 0 then nvl(intime.actual_center_arr_time,intime.actual_collect_arr_time) end )   as all_arr_cnt -- 首集散+中心到件数量
    , intime.dt as dt
from jms_dm.dm_waybill_collect_intime intime
    left join
        jms_dm.dm_waybill_false_taking_dt false_taking
            on  intime.dt = false_taking.dt
            and intime.waybill = false_taking.waybill_no
            and false_taking.dt between date_sub('{{ execution_date | cst_ds }}', 7) and '{{ execution_date | cst_ds }}'
    left join
        jms_dim.dim_network_whole_massage  dim_network
            on intime.receive_network_code = dim_network.code
where
    intime.dt between date_sub('{{ execution_date | cst_ds }}', 7) and '{{ execution_date | cst_ds }}'
    and intime.is_over is not null and intime.type = 0  -- 只统计应交件
group by
      intime.taking_date
    , intime.receive_network_code
    , intime.receive_network_name
    , intime.receive_franchisee_code
    , intime.receive_franchisee
    , intime.receive_proxy_code
    , intime.receive_proxy
    , dim_network.virt_code
    , dim_network.virt_name
    , intime.order_source
    , intime.taking_user_code
    , intime.taking_user_name
    , intime.customer_code
    , intime.customer_name
    , intime.dt
distribute by dt
;